package cn.hd01.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import cn.hd01.repository.entity.OrderProduct;

@Repository
public interface OrderProductRepository extends JpaRepository<OrderProduct, Integer> {
	
	Page<OrderProduct> findAll(Specification<OrderProduct> spec, Pageable pageable);
	
	Page<OrderProduct> findAll(Specification<OrderProduct> spec);
	
	@Query(value = "SELECT cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price, sum(discount_sale_price) as  discount_sale_price, sum(operation_count) as operation_count, cp.create_time,cp.update_time from  c_order_product cp LEFT JOIN product p on cp.product_id = p.id where 1=1 and p.parent_id is not null and DATE_FORMAT(cp.create_time, '%Y-%m-%d')>=DATE_FORMAT(?1, '%Y-%m-%d') and DATE_FORMAT(cp.create_time, '%Y-%m-%d') <=DATE_FORMAT(?2, '%Y-%m-%d') GROUP BY product_id LIMIT ?3, ?4", nativeQuery = true)
	List<OrderProduct> pageOrderProduct(String startDate,String endDate,int begin,int pageSize);
	
	@Query(value ="SELECT COUNT(1) FROM (SELECT  cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price,sum(discount_sale_price) as  discount_sale_price,sum(operation_count) as operation_count,cp.create_time,cp.update_time FROM c_order_product cp LEFT JOIN product p on cp.product_id = p.id where 1=1 and p.parent_id is not null and DATE_FORMAT(cp.create_time, '%Y-%m-%d')>=DATE_FORMAT(?1, '%Y-%m-%d') and DATE_FORMAT(cp.create_time, '%Y-%m-%d') <=DATE_FORMAT(?2, '%Y-%m-%d') GROUP BY product_id) aa ", nativeQuery = true)
	Integer pageOrderProductCount(String startDate,String endDate);
	
	@Query(value = "SELECT  cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price,sum(discount_sale_price) as  discount_sale_price,sum(cp.operation_count) as operation_count, cp.create_time,cp.update_time from c_order_product cp LEFT JOIN product p on cp.product_id = p.id  where 1=1 and p.parent_id is not null  GROUP BY product_id LIMIT ?1, ?2", nativeQuery = true)
	List<OrderProduct> pageOrderProduct(int begin,int pageSize);
	
	@Query(value ="SELECT COUNT(1) FROM (SELECT  cp.id,cp.order_id,cp.product_name,cp.product_id,sum(product_sale_price) as product_sale_price,sum(discount_sale_price) as  discount_sale_price,sum(operation_count) as operation_count,cp.create_time,cp.update_time FROM c_order_product cp LEFT JOIN product p on cp.product_id = p.id where 1=1 and p.parent_id is not null GROUP BY product_id) aa", nativeQuery = true)
	Integer pageOrderProductCount();
	
	@Query(value = "select count(1) as count from customer_operation where 1=1 and product_id = ?1", nativeQuery = true)
	Integer getProductCount(Integer id);
	
	List<OrderProduct> findByOrderId(Integer orderId);
	
	@Modifying
	@Transactional
	@Query(value = "delete from c_order_product where order_id = ?1", nativeQuery = true)
	int deleteByOrderId(Integer orderId);
	
	@Modifying
	@Transactional
	@Query(value = "update c_order_product set product_name = ?1 where product_id = ?2", nativeQuery = true)
	int updateProductName(String productName, Integer productId);
}
